#coding=utf8

import MySQLdb
import config
import pub
import time
import traceback
#from util.sshtunnel import SSHTunnelForwarder


#链接池定义
connections = { }

DB_HOST = config.config.get("DB","HOST")
DB_PORT = int(config.config.get("DB","PORT"))
DB_USER = config.config.get("DB","USER")
DB_PASSWORD = config.config.get("DB","PASSWORD")
DB_NAME = config.config.get("DB","DB_NAME")
DB_CHARSET = config.config.get("DB","CHARSET")
#获取链接 - 若链接已经存在，则直接返回现有的链接，否则需要新建链接
def get_db_conn():
    conn = MySQLdb.connect(DB_HOST,port=DB_PORT,user=DB_USER,passwd=DB_PASSWORD,db=DB_NAME,charset=DB_CHARSET)
    return conn
    '''
    db_id = 0
    if(connections.has_key(db_id)):
        conn = connections.get(db_id)
        #先进行ping，看连接是否还处于活动状态，若处于活动则直接返回，否则重新连接
        try:
            conn.ping()
            return conn
        except:
            try:
                conn = MySQLdb.connect(DB_HOST,port=DB_PORT,user=DB_USER,passwd=DB_PASSWORD,db=DB_NAME,charset=DB_CHARSET)
                connections[db_id] = conn
                return conn
            except:
                pub.log("","数据库连接异常",traceback.format_exc())
                return None
    else:
        conn = MySQLdb.connect(DB_HOST,port=DB_PORT,user=DB_USER,passwd=DB_PASSWORD,db=DB_NAME,charset=DB_CHARSET)
        connections[db_id] = conn
        return conn
    '''
#关闭所有的数据库链接
def close_connections():
    for key,value in connections.items():
        value.close()

#判断bit值是否为true
def bit_is_true(val):
    return val == '\x01'

#判断是否存在
def is_exists(table,where=""):
    try:
        sql = "select 1 from " + config.DB_PREFIX + table
        if(where!=""):
            sql+=" where " +where
        cursor  = get_db_conn().cursor()
        cursor.execute(sql)
        cds   = cursor.fetchone()
        cursor.close()
        if(cds==None):
            return False
        else:
            return True
    except:
        return False

#获取一条记录
def get_one(sql,params=()):
    try:
        conn = get_db_conn()
        cursor  = conn.cursor(cursorclass = MySQLdb.cursors.DictCursor)
        cursor.execute(sql,params)
        cds   = cursor.fetchone()
        cursor.close()
        return cds
    except:
        pub.write_log(traceback.format_exc())
        pub.write_log(sql)
        return None

#获取列表
def get_list(sql,params=()):
    try:
        cursor  = get_db_conn().cursor(cursorclass = MySQLdb.cursors.DictCursor)
        cursor.execute(sql,params)
        cds   = cursor.fetchall()
        cursor.close()
        return cds
    except:
        pub.write_log(traceback.format_exc())
        return None
#执行SQL语句
def insert(sql,params):
    try:
        conn    = get_db_conn()
        cursor  = conn.cursor()
        cursor.execute(sql,params)
        cursor.close()
        conn.commit()
        return cursor.lastrowid
    except Exception,errmsg:
        pub.write_log(traceback.format_exc())
#执行更新语句
def update(sql,params):
    try:
        conn    = get_db_conn()
        cursor  = conn.cursor()
        rowcount = cursor.execute(sql,params)
        cursor.close()
        conn.commit()
        return True
    except Exception,errmsg:
        pub.write_log(traceback.format_exc())
        return False
#存储
def save(table,data,where=None,whereParams = []):
    sql = "update " + config.DB_PREFIX + table + " set "
    params = []
    i = 0
    for key, value in data.items():
        if(i > 0): sql += ","
        sql += key + "=%s"
        params.append(value)
        i = i + 1
    if(where):
        sql += " where " + where
        if(whereParams):
            for i in range(0,len(whereParams)):
                params.append(whereParams[i])
    #print sql
    #print params
    return update(sql, params)
#执行语句
def execute(sql,params):
    try:
        conn    = get_db_conn()
        cursor  = conn.cursor()
        rowcount = cursor.execute(sql,params)
        cursor.close()
        conn.commit()
        return rowcount
    except Exception,errmsg:
        pub.write_log(traceback.format_exc())
        return 0